﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Text;
using Xwltz.Framework.Helper;

namespace Xwltz.Framework.Database
{
    /// <summary>
    /// [ADO.NET + reflection] 简易ORM客户端
    /// </summary>
    public static class SimpleClient
    {
        /// <summary>
        /// 获取数据连接
        /// </summary>
        public static SqlServerDbHelper Db = new SqlServerDbHelper();

        #region 新增方法

        /// <summary>
        /// 新增模型
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int AddModel<TEntity>(TEntity model) where TEntity : class, new()
        {
            var identity = false;
            var strSql = new StringBuilder();
            var str1 = new StringBuilder();
            var str2 = new StringBuilder();
            var properties = model.GetType().GetProperties();
            var tableName = Db.GetTableName(model.GetType());

            var paras = new List<DbParameter>();

            strSql.Append("INSERT INTO " + tableName + "(");
            foreach (var propertyInfo in properties)
            {
                if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XNotMappedAttribute), false).Length > 0 || propertyInfo.GetValue(model, null) == null) continue;
                if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XKeyAttribute), false).Length == 0 && propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XIdentityKeyAttribute), false).Length == 0)
                {
                    str1.Append(propertyInfo.Name + ",");
                    str2.Append("@" + propertyInfo.Name + ",");
                    paras.Add(new SqlParameter("@" + propertyInfo.Name, propertyInfo.GetValue(model, null)));
                }
                else
                {
                    identity = true;
                }
            }

            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") VALUES (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(identity ? ";SELECT @@IDENTITY;" : ";");
            var obj = identity ? Db.ExcuteNonQuery(strSql.ToString(), false, true, paras.ToArray()) : Db.ExcuteScalar(strSql.ToString(), false, true, paras.ToArray());
            return obj.ToInt();
        }

        #endregion

        #region 删除方法

        /// <summary>
        /// 删除模型
        /// 该删除方法只支持单主键，如果有多主键请使用模型删除
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="id"></param>
        /// <param name="idName"></param>
        /// <returns></returns>
        public static int DeleteModel<TEntity>(object id, string idName = "") where TEntity : class, new()
        {
            var model = typeof(TEntity);
            var tableName = Db.GetTableName(model);
            var strSql = new StringBuilder();
            var paras = new List<DbParameter>();
            if (string.IsNullOrEmpty(idName))
            {

                var properties = model.GetProperties();
                var array = properties;
                var text = "";
                var str = "";
                foreach (var propertyInfo in array)
                {
                    if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XKeyAttribute), false).Length > 0)
                    {
                        text = "delete from " + tableName + " where " + propertyInfo.Name + "=@" + propertyInfo.Name;
                        str = propertyInfo.Name;
                    }
                }
                strSql.Append(text);
                paras.Add(new SqlParameter("@" + str, id));
            }
            else
            {
                strSql.Append("delete " + tableName + " where " + idName + "=@" + idName);
                paras.Add(new SqlParameter("@" + idName, id));
            }

            return Db.ExcuteNonQuery(strSql.ToString(), false, true, paras.ToArray());
        }

        /// <summary>
        /// 删除模型
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public static int DeleteModel<TEntity>(TEntity model) where TEntity : class, new()
        {
            var tableName = Db.GetTableName(model.GetType());
            var properties = model.GetType().GetProperties();
            var strSql = new StringBuilder();
            var paras = new List<DbParameter>();

            strSql.Append("delete " + tableName + " where 1=1");
            var list = new List<string>();
            
            foreach (var propertyInfo in properties)
            {
                if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XKeyAttribute), false).Length > 0)
                {
                    list.Add(propertyInfo.Name);
                }
            }
            list.ForEach(a => {
                strSql.Append(" and " + a + "=@" + a);
                paras.Add(new SqlParameter(a, properties.FirstOrDefault(b => b.Name.Equals(a))?.GetValue(model, null)));
            });

            return Db.ExcuteNonQuery(strSql.ToString(), false, true, paras.ToArray());
        }

        #endregion

        #region 修改方法
        /// <summary>
        /// 修改模型
        /// </summary>
        /// <typeparam name="TEntity">模型</typeparam>
        /// <returns></returns>
        public static int UpdateModel<TEntity>(TEntity model) where TEntity : class, new()
        {
            var type = typeof(TEntity);
            var strSql = new StringBuilder();
            var strTemp = new StringBuilder();
            var paras = new List<DbParameter>();
            var primaryKey = "";

            var properties = type.GetProperties();

            strSql.Append("update " + Db.GetTableName(type) + " set ");
            foreach (var propertyInfo in properties)
            {
                if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XNotMappedAttribute), false).Length > 0 || propertyInfo.GetValue(model, null) == null) continue;
                if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XKeyAttribute), false).Length == 0)
                {
                    strTemp.Append(propertyInfo.Name + "=@" + propertyInfo.Name + ",");
                    paras.Add(new SqlParameter("@" + propertyInfo.Name, propertyInfo.GetValue(model, null)));
                }
                else
                {
                    primaryKey = propertyInfo.Name;
                    paras.Add(new SqlParameter("@" + propertyInfo.Name, propertyInfo.GetValue(model, null)));
                }
            }

            strSql.Append(strTemp.ToString().Trim(','));
            strSql.Append(" where " + primaryKey + "=@" + primaryKey);
            return Db.ExcuteNonQuery(strSql.ToString(), false, true, paras.ToArray());
        }

        /// <summary>
        /// 修改模型 忽略字段
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="model"></param>
        /// <param name="ignore">忽略的字段</param>
        /// <returns></returns>
        public static int UpdateModel<TEntity>(TEntity model, string[] ignore) where TEntity : class, new()
        {
            var type = typeof(TEntity);
            var strSql = new StringBuilder();
            var strTemp = new StringBuilder();
            var paras = new List<DbParameter>();
            var primaryKey = "";

            var properties = type.GetProperties();

            strSql.Append("update " + Db.GetTableName(type) + " set ");
            foreach (var propertyInfo in properties)
            {
                if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XNotMappedAttribute), false).Length > 0 || propertyInfo.GetValue(model, null) == null) continue;
                if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XKeyAttribute), false).Length == 0)
                {
                    if (!ignore.Contains(propertyInfo.Name))
                    {
                        strTemp.Append(propertyInfo.Name + "=@" + propertyInfo.Name + ",");
                        paras.Add(new SqlParameter("@" + propertyInfo.Name, propertyInfo.GetValue(model, null)));
                    }
                }
                else
                {
                    primaryKey = propertyInfo.Name;
                    paras.Add(new SqlParameter("@" + propertyInfo.Name, propertyInfo.GetValue(model, null)));
                }
            }

            strSql.Append(strTemp.ToString().Trim(','));
            strSql.Append(" where " + primaryKey + "=@" + primaryKey);
            return Db.ExcuteNonQuery(strSql.ToString(), false, true, paras.ToArray());
        }

        /// <summary>
        /// 修改模型 更新字段
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="model"></param>
        /// <param name="column">更新的字段</param>
        /// <returns></returns>
        public static int UpdateModel<TEntity>(TEntity model, object[] column) where TEntity : class, new()
        {
            var type = typeof(TEntity);
            var strSql = new StringBuilder();
            var strTemp = new StringBuilder();
            var paras = new List<DbParameter>();
            var primaryKey = "";

            var properties = type.GetProperties();

            strSql.Append("update " + Db.GetTableName(type) + " set ");
            foreach (var propertyInfo in properties)
            {
                if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XNotMappedAttribute), false).Length > 0 || propertyInfo.GetValue(model, null) == null) continue;
                if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XKeyAttribute), false).Length == 0)
                {
                    if (column.Contains(propertyInfo.Name))
                    {
                        strTemp.Append(propertyInfo.Name + "=@" + propertyInfo.Name + ",");
                        paras.Add(new SqlParameter("@" + propertyInfo.Name, propertyInfo.GetValue(model, null)));
                    }
                }
                else
                {
                    primaryKey = propertyInfo.Name;
                    paras.Add(new SqlParameter("@" + propertyInfo.Name, propertyInfo.GetValue(model, null)));
                }
            }

            strSql.Append(strTemp.ToString().Trim(','));
            strSql.Append(" where " + primaryKey + "=@" + primaryKey);
            return Db.ExcuteNonQuery(strSql.ToString(), false, true, paras.ToArray());
        }

        #endregion

        #region 查询方法

        /// <summary>
        /// 根据主键查询
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="id"></param>
        /// <param name="idName"></param>
        /// <returns></returns>
        public static TEntity GetById<TEntity>(dynamic id, string idName = "") where TEntity : class, new()
        {
            var type = typeof(TEntity);
            var strSql = new StringBuilder();
            var paras = new List<DbParameter>();
            if (string.IsNullOrEmpty(idName))
            {
                var properties = type.GetProperties();
                var array = properties;
                foreach (var propertyInfo in array)
                {
                    if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XNotMappedAttribute), false).Length > 0 || propertyInfo.GetValue(id, null) == null) continue;

                    if (propertyInfo.GetCustomAttributes(typeof(DataBaseHelper.XKeyAttribute), false).Length > 0)
                    {
                        strSql.Append("select top 2 * from " + Db.GetTableName(type) + " where " + propertyInfo.Name + "=@" + propertyInfo.Name);
                        paras.Add(new SqlParameter("@" + propertyInfo.Name, id));
                    }
                }
            }
            else
            {
                strSql.Append("select top 2 * from " + Db.GetTableName(type) + " where " + idName + "=@" + idName);
                paras.Add(new SqlParameter("@" + idName, id));
            }

            if (paras.Count <= 0) return new TEntity();

            var dataTable = Db.ExcuteTable(strSql.ToString(), false, paras.ToArray());
            if (dataTable.Rows.Count == 0)
            {
                throw new Exception("not found record");
            }
            if (dataTable.Rows.Count == 1)
            {
                return DataBaseHelper.TableToList<TEntity>(dataTable)[0];
            }
            if (dataTable.Rows.Count > 1)
            {
                throw new Exception("find two or more record");
            }
            return new TEntity();
        }

        /// <summary>
        /// 获取条数
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static object GetCount<TEntity>(string strWhere)
        {
            var type = typeof(TEntity);
            var tableName = Db.GetTableName(type);
            var recordCount = Convert.ToInt32(Db.ExcuteScalar("SELECT COUNT(1) AS RecordCount FROM " + tableName + " where 1=1 " + strWhere));
            return recordCount;
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <returns></returns>
        public static List<TEntity> GetList<TEntity>() where TEntity : class, new()
        {
            var type = typeof(TEntity);
            var strSql = new StringBuilder();

            strSql.Append("SELECT * FROM " + Db.GetTableName(type));
            var dt = Db.ExcuteTable(strSql.ToString());
            return DataBaseHelper.TableToList<TEntity>(dt);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="strSql"></param>
        /// <param name="isProc"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public static List<TEntity> GetList<TEntity>(string strSql, bool isProc = false, params DbParameter[] args) where TEntity : class, new()
        {
            var dt = Db.ExcuteTable(strSql, isProc, args);
            return DataBaseHelper.TableToList<TEntity>(dt);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="top">前几条数据</param>
        /// <param name="strWhere">条件</param>
        /// <param name="strOrder">排序</param>
        /// <returns></returns>
        public static List<TEntity> GetList<TEntity>(int top, string strWhere = "", string strOrder = "") where TEntity : new()
        {
            var type = typeof(TEntity);
            var strSql = new StringBuilder();

            if (top != 0)
            {
                strSql.Append("SELECT TOP " + top + " * FROM ");
            }
            else
            {
                strSql.Append("SELECT * FROM ");
            }

            strSql.Append(Db.GetTableName(type));

            if (!strWhere.IsNullOrEmpty()) strSql.Append(" " + strWhere + " ");
            if (!strOrder.IsNullOrEmpty()) strSql.Append(" " + strOrder + " ");

            var dt = Db.ExcuteTable(strSql.ToString());
            return DataBaseHelper.TableToList<TEntity>(dt);
        }

        /// <summary>
        /// 分页列表
        /// </summary>
        /// <typeparam name="TEntity">模型</typeparam>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="strOrder"></param>
        /// <param name="strWhere"></param>
        /// <param name="strSelect"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public static List<TEntity> GetListPage<TEntity>(out int recordCount, string strOrder = "", string strSelect = "*", string strWhere = "", int pageSize = 10, int pageIndex = 1) where TEntity : class, new()
        {
            var type = typeof(TEntity);
            var strSql = new StringBuilder();

            var tableName = Db.GetTableName(type);

            recordCount = Convert.ToInt32(Db.ExcuteScalar("SELECT COUNT(1) AS RecordCount FROM "+ tableName + " where 1=1 " + strWhere));
            int count = pageSize * (pageIndex - 1);

            if (strOrder.IsNullOrEmpty())
            {
                throw new Exception("orderby is a necessary parameter");
            }

            //strSql.Append("select * from (select " + strSelect + ",ROW_NUMBER()  over( order by " + strOrder + " ) as rownum from " + Db.GetTableName(type) + " where 1=1 " + strWhere + ") t2 where rownum between " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize);
            strSql.Append("SELECT t2.n, t1.* FROM " + tableName + " t1, (SELECT TOP " + (pageSize * count) + " row_number() OVER (ORDER BY " + strOrder + ") n, ID FROM " + tableName + " WHERE 1=1 " + strWhere + ") t2 WHERE 1=1 AND t1.id = t2.id AND t2.n > " + count + "  ORDER BY t2.n asc");
            
            var table = Db.ExcuteTable(strSql.ToString());
            return DataBaseHelper.TableToList<TEntity>(table);
        }

        #endregion

        #region 通用方法

        /// <summary>
        /// 获取实体类的字典模型
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="model"></param>
        /// <returns></returns>
        public static Dictionary<string, string> GetProperties<TEntity>(TEntity model)
        {
            Dictionary<string, string> ret = new Dictionary<string, string>();

            if (model == null) return null;
            var properties = model.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

            if (properties.Length <= 0) return null;
            foreach (var propertyInfo in properties)
            {
                string name = propertyInfo.Name;
                string value = FormartFiledValueByType(propertyInfo.PropertyType.FullName, propertyInfo.GetValue(model, null));
                if (propertyInfo.PropertyType.IsValueType || propertyInfo.PropertyType.Name.StartsWith("String")) ret.Add(name, value);
            }

            return ret;
        }

        /// <summary>
        /// 根据数据库字段格式化对应的C#值类型
        /// </summary>
        /// <param name="typeName"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static string FormartFiledValueByType(string typeName, object obj)
        {
            string value = "";
            if (typeName.Contains("System.String"))
            {
                value = Convert.ToString(obj); return value;
            }
            if (typeName.Contains("System.Int32"))
            {
                value = Convert.ToInt32(obj).ToString(); return value;
            }
            if (typeName.Contains("System.DateTime"))
            {
                value = Convert.ToDateTime(obj).ToString(CultureInfo.InvariantCulture); return value;
            }
            if (typeName.Contains("System.Int64"))
            {
                value = Convert.ToInt64(obj).ToString(); return value;
            }
            if (typeName.Contains("System.Single"))
            {
                value = Convert.ToSingle(obj).ToString(CultureInfo.InvariantCulture); return value;
            }
            if (typeName.Contains("System.Double"))
            {
                value = Convert.ToDouble(obj).ToString(CultureInfo.InvariantCulture); return value;
            }
            if (typeName.Contains("System.Decimal"))
            {
                value = Convert.ToDecimal(obj).ToString(CultureInfo.InvariantCulture); return value;
            }
            if (typeName.Contains("System.Boolean"))
            {
                value = Convert.ToBoolean(obj).ToString(); return value;
            }

            return value;
        }

        #endregion
    }
}
